import pandas as pd
import numpy as np
import os
import re
import xlrd
from openpyxl import Workbook, load_workbook
from openpyxl.styles import *


df = pd.read_excel(r'D:\pycharm\temp\baobiao_name\name.xls')






def delete_num(aStr):
    """
    :param aStr:
    :return:
    """
    if aStr == '':
        return aStr
    else:
        return ''.join([v for v in aStr if v not in ['0', '1', '2', '3', '4', '5', '6', '7', '8', '9']])


level_1 = []
# level_2 = []
names = []
name_in = []
for filepath, _, filenames in os.walk(r'D:\pycharm\temp\baobiao_name\files'):
    if filenames == []:
        pass
    else:
        for filename in filenames:
            # print(filepath, filename)
            pathTmp = [delete_num(aStr) for aStr in filepath[35:].split('\\')]
            level_1.append(pathTmp[0])
            # level_2.append(pathTmp[1])
            nameTmp = filename.split('.')[0]
            names.append(nameTmp)
            # tmp = pd.read_excel(filepath + '\\' + filename, sheet_name='基础量纲')
            if filename.split('.')[1] == 'xls':
                workbook = xlrd.open_workbook(filepath + '\\' + filename)
                # workbook = xlrd.open_workbook(r'D:\pycharm\temp\baobiao_name\files\02市场管理\03有序用电\120.有序用电表B1_电力供需情况.xls')
                table = workbook.sheet_by_index(0) #通过索引顺序获取
                for row in table.row(2):
                    if row.value != '':
                        tmp = str(row.value)
                        # print(row.value)
                # for row in table.row(0):
                #     # if row.value != '':
                #     #     tmp_0 = row.value
                # if '表' in tmp_2:
                #     tmp = tmp_2
                # else:
                #     tmp = tmp_0
                name_in.append(tmp)
            # elif filename.split('.')[1:][1] == 'xlsx':
            #     path_ = filepath + '\\' + filename
            #     wb = load_workbook(path_)
            #     # openpyxl只能处理 .xlsx 合适的表格
            #     sheet = wb.worksheets[0]
            #     tmp =sheet[1][1].value # 返回由该行中所有的单元格对象组成的列表
            #     name_in.append(tmp)
            print(pathTmp[0], nameTmp, tmp)

df = pd.DataFrame(((a, c, d) for a, c, d in zip(level_1, names, name_in)))
df.to_csv(r'D:\pycharm\temp\baobiao_name\name.csv', encoding='gbk', index=False)